#!/bin/bash

# 数据一致性检查和清理脚本

RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

echo "🔍 SPAM3-UK 数据一致性检查报告"
echo "================================================"
echo "检查时间: $(date)"
echo ""

# 检查服务器状态
echo -n "检查API服务器状态... "
if curl -s http://localhost:5700/health > /dev/null 2>&1; then
    echo -e "${GREEN}✅ 运行正常${NC}"
else
    echo -e "${RED}❌ 服务器未启动${NC}"
    exit 1
fi

echo ""
echo "📊 数据库数据统计"
echo "----------------------------------------"

# 数据库统计
DB_TOTAL=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM make_info_uk;")
echo "数据库总记录数: ${DB_TOTAL}"

echo ""
echo "📋 车型分布统计"
echo "----------------------------------------"
mysql -u root -p123 -D dcp-cloud -e "SELECT model, COUNT(*) as count FROM make_info_uk GROUP BY model ORDER BY count DESC, model;"

echo ""
echo "🔍 数据完整性检查"
echo "----------------------------------------"

# 检查重复记录
DUPLICATES=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM (SELECT model, variant, COUNT(*) as cnt FROM make_info_uk GROUP BY model, variant HAVING cnt > 1) as dupes;")
echo -n "重复记录检查... "
if [ "$DUPLICATES" -eq 0 ]; then
    echo -e "${GREEN}✅ 无重复记录${NC}"
else
    echo -e "${RED}❌ 发现 ${DUPLICATES} 组重复记录${NC}"
fi

# 检查空价格
NULL_PRICES=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM make_info_uk WHERE price_from IS NULL OR price_from = 0;")
echo -n "价格完整性检查... "
if [ "$NULL_PRICES" -eq 0 ]; then
    echo -e "${GREEN}✅ 所有记录都有价格${NC}"
else
    echo -e "${YELLOW}⚠️ ${NULL_PRICES} 条记录缺少价格${NC}"
fi

# 检查缺少URL的记录
NO_URL=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM make_info_uk WHERE detail_url IS NULL OR detail_url = '';")
echo -n "详情URL完整性检查... "
if [ "$NO_URL" -eq 0 ]; then
    echo -e "${GREEN}✅ 所有记录都有详情URL${NC}"
else
    echo -e "${YELLOW}⚠️ ${NO_URL} 条记录缺少详情URL${NC}"
fi

# 检查基本字段完整性
INCOMPLETE=$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(*) FROM make_info_uk WHERE model IS NULL OR model = '' OR variant IS NULL OR variant = '';")
echo -n "基本信息完整性检查... "
if [ "$INCOMPLETE" -eq 0 ]; then
    echo -e "${GREEN}✅ 所有记录信息完整${NC}"
else
    echo -e "${RED}❌ ${INCOMPLETE} 条记录信息不完整${NC}"
fi

echo ""
echo "🌐 API数据一致性检查"
echo "----------------------------------------"

# 检查API返回数据
API_COUNT=$(curl -s "http://localhost:5700/api/vehicles?page_size=100" | jq '.data | length' 2>/dev/null)
API_TOTAL=$(curl -s "http://localhost:5700/api/vehicles?page_size=100" | jq '.total' 2>/dev/null)

echo "API返回记录数: ${API_COUNT}"
echo "API报告总数: ${API_TOTAL}"

echo -n "数据库与API一致性... "
if [ "$DB_TOTAL" -eq "$API_COUNT" ] && [ "$DB_TOTAL" -eq "$API_TOTAL" ]; then
    echo -e "${GREEN}✅ 数据完全一致${NC}"
else
    echo -e "${RED}❌ 数据不一致${NC}"
    echo "  数据库: ${DB_TOTAL} 条"
    echo "  API返回: ${API_COUNT} 条"
    echo "  API总数: ${API_TOTAL} 条"
fi

echo ""
echo "🔧 前端页面车型显示检查"
echo "----------------------------------------"

# 检查前端页面能获取的车型数
echo -n "检查前端页面数据加载... "
FRONTEND_TEST=$(curl -s "http://localhost:5700/web/" | grep -o "总车辆数" | wc -l)
if [ "$FRONTEND_TEST" -gt 0 ]; then
    echo -e "${GREEN}✅ 前端页面正常${NC}"
else
    echo -e "${YELLOW}⚠️ 前端页面可能有问题${NC}"
fi

echo ""
echo "📈 分类分布检查"
echo "----------------------------------------"
mysql -u root -p123 -D dcp-cloud -e "SELECT vehicle_category, COUNT(*) as count FROM make_info_uk GROUP BY vehicle_category ORDER BY count DESC;"

echo ""
echo "🏷️ 燃料类型分布"
echo "----------------------------------------"
mysql -u root -p123 -D dcp-cloud -e "SELECT fuel_type, COUNT(*) as count FROM make_info_uk GROUP BY fuel_type ORDER BY count DESC;"

echo ""
echo "💰 价格范围分析"
echo "----------------------------------------"
mysql -u root -p123 -D dcp-cloud -e "SELECT 
    MIN(price_from) as min_price,
    MAX(price_from) as max_price,
    AVG(price_from) as avg_price,
    COUNT(*) as total_records
FROM make_info_uk;"

echo ""
echo "🕒 数据新鲜度检查"
echo "----------------------------------------"
OLDEST=$(mysql -u root -p123 -D dcp-cloud -se "SELECT MIN(created_at) FROM make_info_uk;")
NEWEST=$(mysql -u root -p123 -D dcp-cloud -se "SELECT MAX(created_at) FROM make_info_uk;")
echo "最早记录时间: ${OLDEST}"
echo "最新记录时间: ${NEWEST}"

# 检查是否有超过7天的数据
DAYS_OLD=$(mysql -u root -p123 -D dcp-cloud -se "SELECT DATEDIFF(NOW(), MAX(created_at)) FROM make_info_uk;")
echo -n "数据新鲜度... "
if [ "$DAYS_OLD" -le 7 ]; then
    echo -e "${GREEN}✅ 数据较新 (${DAYS_OLD}天前)${NC}"
elif [ "$DAYS_OLD" -le 30 ]; then
    echo -e "${YELLOW}⚠️ 数据一般 (${DAYS_OLD}天前)${NC}"
else
    echo -e "${RED}❌ 数据过旧 (${DAYS_OLD}天前)${NC}"
fi

echo ""
echo "🧹 数据清理建议"
echo "----------------------------------------"

# 检查是否需要清理
SUGGESTIONS=0

if [ "$DUPLICATES" -gt 0 ]; then
    echo "❗ 建议清理重复记录"
    SUGGESTIONS=$((SUGGESTIONS + 1))
fi

if [ "$NULL_PRICES" -gt 0 ]; then
    echo "❗ 建议补全价格信息或删除无价格记录"
    SUGGESTIONS=$((SUGGESTIONS + 1))
fi

if [ "$DAYS_OLD" -gt 30 ]; then
    echo "❗ 建议重新抓取数据以确保数据新鲜度"
    SUGGESTIONS=$((SUGGESTIONS + 1))
fi

if [ "$SUGGESTIONS" -eq 0 ]; then
    echo -e "${GREEN}✅ 数据质量良好，无需清理${NC}"
fi

echo ""
echo "📊 总结报告"
echo "=========================================="
echo -e "数据库记录总数: ${BLUE}${DB_TOTAL}${NC}"
echo -e "车型数量: ${BLUE}$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(DISTINCT model) FROM make_info_uk;")${NC}"
echo -e "分类数量: ${BLUE}$(mysql -u root -p123 -D dcp-cloud -se "SELECT COUNT(DISTINCT vehicle_category) FROM make_info_uk;")${NC}"
echo -e "数据一致性: ${GREEN}API与数据库${NC}"
echo -e "数据质量: ${GREEN}良好${NC}"

if [ "$SUGGESTIONS" -eq 0 ]; then
    echo -e "状态: ${GREEN}✅ 所有检查通过${NC}"
else
    echo -e "状态: ${YELLOW}⚠️ 有${SUGGESTIONS}项需要关注${NC}"
fi

echo ""
echo "🔄 下次建议操作："
echo "1. 定期运行此脚本检查数据一致性"
echo "2. 每周重新抓取一次数据保持新鲜度"
echo "3. 监控API响应时间和数据完整性"
echo "4. 备份数据库以防数据丢失"
